﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Common;
using DevExpress.XtraEditors;
using DevExpress.XtraGrid;
using DevExpress.XtraGrid.Views.Grid;

namespace CNCSoft.ErpApps.TMS
{
    public partial class frmDiemThi : Form
    {
        private SqlConnection conn = new SqlConnection(GlobalVariable.WSInfo.ConnectionString);
        private DataSet ds = new DataSet();

        public frmDiemThi()
        {
            InitializeComponent();
        }

        private void frmDiemThi_Load(object sender, EventArgs e)
        {
            LoadControl();
        }

        private void searchLookUpEditBatch_EditValueChanged(object sender, EventArgs e)
        {
            try
            {
                string maBatch = searchLookUpEditBatch.EditValue.ToString();
                if (searchLookUpEditSubject.EditValue.ToString() != null && searchLookUpEditSubject.EditValue.ToString() != "" && searchLookUpEditSubject.EditValue.ToString() != "System.Data.DataRowView")
                {
                    LoadGrid(maBatch, searchLookUpEditSubject.EditValue.ToString());
                }
            }
            catch (Exception ex)
            { MessageBox.Show(ex.Message); }
        }

        private void searchLookUpEditSubject_EditValueChanged(object sender, EventArgs e)
        {
            try
            {
                string batch = searchLookUpEditBatch.EditValue.ToString();
                string mamonthi = searchLookUpEditSubject.EditValue.ToString();
                LoadGrid(batch, mamonthi);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            UpdateData();
        }

        #region coding
        //Load data to grid depend on batch
        private void LoadGrid(string mabatch)
        {
            string sql = @"SELECT kqht.ID, inv.InvoiceNumber, enq.Ten , kqht.DiemThi, kqht.GhiChu FROM
                    Invoice inv inner join Enquiry enq on inv.EnquiryNumber = enq.EnquiryNumber
                    inner join Invoice_KhoaHoc invkh on  invkh.InvoiceNumber = inv.InvoiceNumber
                    inner join Invoice_KhoaHoc_KQHT kqht on invkh.ID = kqht.MaHocTap
                    WHERE invkh.MaKhoaHoc = '" + mabatch + "'";
            ExecDataAdapter(sql, "TableDiemThi", grcData);
        }

        private void LoadGrid(string mabatch, string mamonthi)
        {
            string sql = @"SELECT kqht.ID, inv.InvoiceNumber, enq.Ten , kqht.DiemThi, kqht.GhiChu FROM
                    Invoice inv inner join Enquiry enq on inv.EnquiryNumber = enq.EnquiryNumber
                    inner join Invoice_KhoaHoc invkh on  invkh.InvoiceNumber = inv.InvoiceNumber
                    inner join Invoice_KhoaHoc_KQHT kqht on invkh.ID = kqht.MaHocTap
                    WHERE invkh.MaKhoaHoc = '" + mabatch + @"' AND kqht.MaMonThi = '" + mamonthi + "'";
            ExecDataAdapter(sql, "TableDiemThi", grcData);
        }

        //Load controls of form
        private void LoadControl()
        {
            try
            {
                string companyID = GlobalVariable.WSInfo.CompanyID;
                ExecDataAdapter(@"SELECT * FROM DM_KHOAHOC 
                                    WHERE MaDonVi = '" + companyID + "'", "DM_KHOAHOC", "MaKhoaHoc", "TenKhoaHoc", searchLookUpEditBatch);
                ExecDataAdapter(@"SELECT dmmonthi.MaMonThi, dmmonthi.TenMonThi FROM
                    DM_MONTHI dmmonthi inner join DM_MONHOC dmmonhoc on dmmonthi.MaMonHoc = dmmonhoc.MaMonHoc
                    WHERE dmmonhoc.MaDonVi = '" + companyID + "'", "DM_MONTHI", "MaMonThi", "TenMonThi", searchLookUpEditSubject);
                ExecDataAdapter(@"SELECT UserID, FullName FROM SYS_List_User
                    WHERE IsLocked = 0", "SYS_List_User", "UserID", "FullName", searchLookUpEditUser);
                dateEdit.EditValue = DateTime.Now.ToShortDateString();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        //common method
        public void ExecDataAdapter(string sql, string tableName, string value, string name, SearchLookUpEdit searchEdit)
        {
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(sql, GlobalVariable.WSInfo.ConnectionString);
                da.Fill(ds, tableName);
                searchEdit.Properties.DataSource = ds.Tables[tableName];
                searchEdit.Properties.DisplayMember = name;
                searchEdit.Properties.ValueMember = value;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public void ExecDataAdapter(string sql, string tableName, GridControl grid)
        {
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(sql, GlobalVariable.WSInfo.ConnectionString);
                ds = new DataSet();
                da.Fill(ds, tableName);
                grid.DataSource = ds.Tables[tableName];
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        //Copy data from gridcontrol to datatable
        private DataTable CopyGridViewToDataTable(GridView grid)
        {
            try
            {
                DataView dv = (DataView)grid.DataSource;
                return dv.Table.DataSet.Tables[0];
            }
            catch (Exception ex)
            {
                MessageBox.Show("Cannot copy to datatable");
                return null;
            }
        }

        //Execute Update Data
        private void UpdateData()
        {
            try
            {
                string batch = searchLookUpEditBatch.EditValue.ToString();
                string subject = searchLookUpEditSubject.EditValue.ToString();
                string user = searchLookUpEditUser.EditValue.ToString();
                string date = String.Format("{0: MM/dd/yyyy}", dateEdit.DateTime);

                DataTable dt = CopyGridViewToDataTable(grvData);

                int i = 0;
                foreach (DataRow dr in dt.Rows)
                {
                    conn.Open();
                    string update = @"UPDATE Invoice_KhoaHoc_KQHT
                            SET DiemThi = '" + dr["DiemThi"].ToString() + @"',
                            GhiChu = '" + dr["GhiChu"].ToString() + @"',
                            NgayThi = '" + date + @"',
                            MaMonThi = '" + subject + @"',
                            UserID = '" + user + @"'
                            WHERE ID = " + Convert.ToInt32(dr["ID"]);
                    SqlCommand cmd = new SqlCommand(update, conn);
                    i = cmd.ExecuteNonQuery();
                    
                    conn.Close();
                    LoadGrid(batch);
                }
                if (i > 0)
                {
                    MessageBox.Show("Thành Công");
                }
                else
                {
                    MessageBox.Show("Thất Bại");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Tất cả không được để trống!!");
                return;
            }
        }
        #endregion
    }
}
